﻿using System;
using System.Text.RegularExpressions;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using LumenWorks.Framework.IO.Csv;

namespace estiglp1112_5959_csharp
{

    public partial class TableControl : UserControl
    {
        //Properties and attributes
        private DataTable dataTable = new DataTable();
        private string conString;
        private bool submitMode = false;
        private const string RecordLabelText = " Records in the Table.";
        private const string submittedLabelText = " Records successfully submitted to the database.";
        private const string errorLabelText = "An error ocurred!!";

        /// <summary>
        /// Gets and sets the mode how data is processed.
        /// Reads the data in the database if true, otherwise it assumes
        /// the data in the table will be submitted into the database.
        /// </summary>
        public bool EnableSubmitMode
        {
            get { return submitMode; }
            set
            {
                submitMode = value;
                this.dataTable.Clear();
                if (value)
                {
                    RefreshButton.Enabled = false;
                    SubmitButton.Enabled = true;
                }
                else
                {
                    RefreshButton.Enabled = true;
                    SubmitButton.Enabled = false;
                    RefreshData();
                }
            }
        }

        /// <summary>
        /// Initializes a new instance of TableControl Class.
        /// </summary>
        /// <param name="Parent">Sets the parent control.</param>
        /// <param name="ConnectionString">Sets the connection String to access the database</param>
        /// <param name="isSubmitMode">Sets the mode how data is processed. See EnableSubmitMode property.</param>
        /// <seealso cref="EnableSubmitMode"/>
        public TableControl(Control Parent, String ConnectionString, bool isSubmitMode)
        {
            InitializeComponent();
            this.Parent = Parent;
            this.Dock = DockStyle.Fill;
            this.Show();
            this.Select();
            this.conString = ConnectionString;

            dataTable.Columns.Add("Código do Docente", typeof(int));
            dataTable.Columns.Add("Nome", typeof(string));
            dataTable.Columns.Add("Último Grau", typeof(string));
            dataTable.Columns.Add("Curso", typeof(string));
            dataTable.Columns.Add("Categoria", typeof(string));
            dataTable.Columns.Add("Regime de Tempo", typeof(string));
            dataTable.Columns.Add("Ano Lectivo", typeof(int));
            dataTable.Columns.Add("Tipo de Estabelecimento", typeof(string));
            dataTable.Columns.Add("Nome do Estabelecimento", typeof(string));

            this.EnableSubmitMode = isSubmitMode;
        }

        private void SubmitButton_Click(object sender, EventArgs e)
        {
            SubmitData();
        }

        /// <summary>
        /// Submits the data in the table into the database.
        /// Works only with EnableSubmitMode property set to True.
        /// </summary>
        public void SubmitData()
        {
            if (this.EnableSubmitMode)
            {
                SQLiteConnection connection = new SQLiteConnection(conString);
                try
                {
                    //connects to database and activates transaction.
                    connection.Open();
                    SQLiteCommand command = connection.CreateCommand();
                    command.Transaction = connection.BeginTransaction();
                    command.CommandType = CommandType.Text;

                    //Set SQL command and parameters
                    command.CommandText = "INSERT INTO DOCENTES VALUES " +
                                           "(@ref, @nome, @grau, @curso, @categoria, " +
                                           "@refime, @ano, @tipo, @estabelecimento)";

                    command.Parameters.Add("@ref", DbType.Int16);
                    command.Parameters.Add("@nome", DbType.String);
                    command.Parameters.Add("@grau", DbType.String);
                    command.Parameters.Add("@curso", DbType.String);
                    command.Parameters.Add("@categoria", DbType.String);
                    command.Parameters.Add("@refime", DbType.String);
                    command.Parameters.Add("@ano", DbType.Int16);
                    command.Parameters.Add("@tipo", DbType.String);
                    command.Parameters.Add("@estabelecimento", DbType.String);

                    //Sets the progress bar
                    this.progressBar.Value = 0;
                    this.progressBar.Maximum = this.dataTable.Rows.Count + 5;

                    //Attributes the data to the parameters and submits the query.
                    foreach (DataRow row in this.dataTable.Rows)
                    {
                        int i = 0;
                        foreach (object cell in row.ItemArray)
                        {
                            command.Parameters[i++].Value = cell.ToString();
                        }
                        command.ExecuteNonQuery();
                        this.progressBar.Value++;
                    }

                    //Commits the transaction and closes
                    command.Transaction.Commit();
                    this.progressBar.Value = this.progressBar.Maximum;
                    connection.Close();
                    this.recordCountLabel.Text = this.dataTable.Rows.Count + submittedLabelText;
                    this.EnableSubmitMode = false;
                    progressBar.Value = 0;
                }
                catch (Exception ex)
                {
                    if (connection.State != ConnectionState.Closed)
                        connection.Close();

                    this.recordCountLabel.Text = errorLabelText;
                    if (MessageBox.Show(this, "Unable to submit data into the database: \n\n" + ex.Message +
                                          "\n\n Do you want to retry?", "Database error",
                                          MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) == DialogResult.Retry)
                    {
                        this.SubmitData();
                    }
                }
            }
        }

        private void RefreshButton_Click(object sender, EventArgs e)
        {
            RefreshData();
        }

        /// <summary>
        /// Refreshes the data in the database.
        /// Works only with EnableSubmitMode property set to False.
        /// </summary>
        public void RefreshData()
        {
            if (this.EnableSubmitMode == false)
            {
                this.dataTable.Clear();
                SQLiteConnection connection = new SQLiteConnection(conString);
                connection.Open();
                SQLiteCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = "SELECT Cod_Docente AS 'Código do Docente', Nome, Grau AS 'Último Grau', Curso, " +
                                      "Categoria, Regime AS 'Regime de Tempo', Ano AS 'Ano Lectivo', " +
                                      "Tipo_Estabelecimento AS 'Tipo de Estabelecimento', Nome_Estabelecimento AS " +
                                      "'Nome do Estabelecimento' FROM DOCENTES";

                //Fill Datatable
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
                dataAdapter.Fill(this.dataTable);

                this.TeachersDataGridView.DataSource = dataTable;
                this.recordCountLabel.Text = this.dataTable.Rows.Count + RecordLabelText;
                connection.Close();

                //dispose connection
                dataAdapter.Dispose();
                command.Dispose();
                connection.Dispose();
            }
        }

        /// <summary>
        /// Loads a Comma Separated Values Table File from the disk and appends it to the table.
        /// Works only with EnableSubmitMode property set to True.
        /// </summary>
        public void importCSV()
        {
            if (this.EnableSubmitMode)
            {
                OpenFileDialog openDialog = new OpenFileDialog();
                openDialog.Multiselect = true;
                openDialog.Filter = "Comma Seperated Values Document (*.txt; *.csv)|*.txt;*.csv|All files (*.*)|*.*";
                openDialog.Title = "Import CSV...";

                if (openDialog.ShowDialog(this) == DialogResult.OK)
                {
                    //multiple file opening support
                    foreach (string path in openDialog.FileNames)
                    {
                        StreamReader stream = new StreamReader(File.Open(path, FileMode.Open));
                        CsvReader csv = new CsvReader(stream, true, ',');

                        this.dataTable.Load(csv);
                        this.TeachersDataGridView.DataSource = dataTable;
                        this.recordCountLabel.Text = dataTable.Rows.Count + RecordLabelText;
                        stream.Close();
                    }
                }
            }
        }
    }
}

